package com.x.erp.dao;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.x.erp.model.City;
import com.x.framework.dao.BaseDao;

@Repository
public class CityDao extends BaseDao {
	private final static String selectCityListSql = "SELECT C.* FROM BASE_CITY C, BASE_PROVINCE P WHERE C.PROVINCE_ID = P.PROVINCE_ID ";

	public List<City> selectCityList(City cityWhere) throws Exception {
		StringBuilder sb = new StringBuilder (selectCityListSql);
		List<Object> params = new ArrayList<Object> ();
		searchCondition (cityWhere, sb, params);
		return this.queryForList (sb.toString (), City.class, params);
	}

	private void searchCondition(City cityWhere, StringBuilder sb, List<Object> params) {
		if (this.isString (cityWhere.getCityId ())) {
			sb.append ("AND C.CITY_ID = ? ");
			params.add (cityWhere.getCityId ());
		}
		if (this.isString (cityWhere.getProvinceName ())) {
			sb.append ("AND P.PROVINCE_NAME = ? ");
			params.add (cityWhere.getProvinceName ());
		}
		if (this.isString (cityWhere.getCityCode ())) {
			sb.append ("AND C.CITY_CODE = ? ");
			params.add (cityWhere.getCityCode ());
		}
		if (this.isString (cityWhere.getCityName ())) {
			sb.append ("AND C.CITY_NAME = ? ");
			params.add (cityWhere.getCityName ());
		}
		if (this.isString (cityWhere.getProvinceId ())) {
			sb.append ("AND C.PROVINCE_ID = ? ");
			params.add (cityWhere.getProvinceId ());
		}
		if (cityWhere.getCitySeq () != null) {
			sb.append ("AND C.CITY_SEQ = ? ");
			params.add (cityWhere.getCitySeq ());
		}
		if (this.isString (cityWhere.getCityPinyin ())) {
			sb.append ("AND C.CITY_PINYIN like ? ");
			params.add (cityWhere.getCityPinyin () + "%");
		}
		if (this.isString (cityWhere.getCityInitial ())) {
			sb.append ("AND C.CITY_INITIAL like ? ");
			params.add (cityWhere.getCityInitial () + "%");
		}
		if (cityWhere.getCityState () != null) {
			sb.append ("AND C.CITY_STATE = ? ");
			params.add (cityWhere.getCityState ());
		}
	}

	private final static String selectCooperationCityListSql = "SELECT " +
			"DISTINCT C.* FROM BASE_CITY C " +
			"JOIN BASE_PROVINCE P ON C.PROVINCE_ID = P.PROVINCE_ID " +
			"JOIN ERP_MARKET M ON C.CITY_ID = M.CITY_ID " +
			"WHERE 1 = 1 ";

	/**
	 * 获取启辕合作市场的城市列表
	 *
	 * @param cityWhere
	 * @return
	 * @throws Exception
	 */
	public List<City> selectCooperationCityList(City cityWhere) throws Exception {
		StringBuilder sb = new StringBuilder (selectCooperationCityListSql);
		List<Object> params = new ArrayList<> ();
		searchCondition (cityWhere, sb, params);
		return this.queryForList (sb.toString (), City.class, params);
	}
}
